package quanlyhochieu.taomoi;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import org.jfree.date.DateUtilities;

import com.sun.corba.se.pept.transport.Connection;

import utils.DateUtils;

import dbutil.ConnectDB;

public class utils {
	
	public utils(){}
	/**************************************
	 * Method name		: insertData
	 * Return type		: int
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static int insertData(String SQL) {
		ConnectDB cont = new ConnectDB();
		cont.connectDB();
		int numberRowsUpdated = -1;
				try {
				numberRowsUpdated = cont.getStatement().executeUpdate(SQL);
			} catch (SQLException se) {
				System.out.println(" SQL is invalid");
				se.printStackTrace();
			}
		return numberRowsUpdated;
	}
	/**************************************
	 * Method name		: deleteVitriLuuTru
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static boolean deleteVitriLuuTru(int id){
		boolean result = false;		
		String sql = "delete from vitriluutru where id = "+id+"";
		ConnectDB cont = new ConnectDB();
		cont.connectDB();
		try{
			int i = cont.getStatement().executeUpdate(sql);
			if (i >0) result = true;
			}catch(Exception ex){
				result = false;
			}
				return result;
		}
	/**************************************
	 * Method name		: updateViTriTu
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static boolean updateViTriTu(String viTriTu, String nganTrongTu,
			String moTa, int id) {
		boolean result = false;		
		String sql = "update vitriluutru set vitritu = '"+viTriTu+"'," +
											" ngantrongtu = '"+nganTrongTu+"'," +
											" mota = '"+moTa+"' where id = "+id;
		System.out.println("SQL UPDATE "+sql);
		ConnectDB cont = new ConnectDB();
		cont.connectDB();
		try{
		int i = cont.getStatement().executeUpdate(sql);
		if (i >0) result = true;
		}catch(Exception ex){
			result = false;
			
		}
			return result;
	}
	/**************************************
	 * Method name		: checkHCTrongDoanRa
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static boolean checkHCTrongDoanRa(String soHC){
		boolean result = false;
		String sql = "select * from dsnguoidi as DS, doanra as DR where DS.sohc = '"+soHC+"' and DS.iddoanra = DR.madoan and DR.ngayve >  '"+DateUtils.utilDateToSqlDate(new java.util.Date())+"'";
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(sql);
		if (array.size() > 0) {
			result = false;
		}
		else result = true;
			return result;
	
	}
	/**************************************
	 * Method name		: checkSoHCVaCMND
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static boolean checkSoHCVaCMND(String soHC){
		boolean result = false;
		String sql = "select cmnd from hochieu where sohc = '"+soHC+"'";
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(sql);
		if (array.size() > 0) {
		 HashMap<String, Object> hash = new HashMap<String, Object>();
		 hash = (HashMap<String, Object>) array.get(0);
		 String sqlCMND = "select * from hochieu where cmnd = '"+hash.get("cmnd").toString()+"'";
		 ArrayList<HashMap<String, Object>> arrayCmnd = ConnectDB.getDataTypeList(sqlCMND);
		 //neu mot nguoi co 2 ho chieu thi ko dc xoa nguoi do ma chi xoa ho chieu can xoa
		 if(arrayCmnd.size() > 1) result = false;	
		 else result = true;
		}
			return result;
	
	}
	/**************************************
	 * Method name		: getCMND
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc Diem
	 **************************************/
	public static String getCMND(String soHC){
		String cmnd = null;
		String sql = "select cmnd from hochieu where sohc = '"+soHC+"'";
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(sql);
		if (array.size() > 0) {
		 HashMap<String, Object> hash = new HashMap<String, Object>();
		 hash = (HashMap<String, Object>) array.get(0);
		 cmnd = hash.get("cmnd").toString();
		}
		 return cmnd;
		
	}
	/**************************************
	 * Method name		: xoaHoChieu
	 * Return type		: boolean
	 * Description		:
	 * Created date		: Aug 20, 2008
	 * Author			: Ngoc DIem
	 **************************************/
	public static boolean xoaHoChieu(String soHC){
		boolean result = false;		
		String sqlHC = "delete from hochieu where sohc = '"+soHC+"'";
		String sqlViTriLuuTru = "delete from vitriluutru where sohc = '"+soHC+"'";
		System.out.println("SQL xoa hc "+sqlHC);
		System.out.println("SQL xoa vi tri luu tru "+sqlViTriLuuTru);
		ConnectDB cont = new ConnectDB();
		cont.connectDB();
		try{
			if(checkSoHCVaCMND(soHC) == false){
				int i = cont.getStatement().executeUpdate(sqlHC);
				int j = cont.getStatement().executeUpdate(sqlViTriLuuTru);
				if (i > 0 && j > 0) result = true;
				else {
					cont.getConnect().rollback();
					result = false;
				}
			}
			else {
				String cmnd = getCMND(soHC);
				String sqlCaNhan = "delete from canhan where cmnd = '"+cmnd+"'";
				int i = cont.getStatement().executeUpdate(sqlHC);
				int j = cont.getStatement().executeUpdate(sqlViTriLuuTru);
				int k =cont.getStatement().executeUpdate(sqlCaNhan);
				if(i > 0 && j > 0 && k >0) result = true;
				else {
					cont.getConnect().rollback();
					result = false;
				}
				
			}
		
		}catch(Exception ex){
			result = false;
			
		}
			return result;
		
	}
	
	public static boolean capNhatData(String sql){
		boolean result = false;		
		ConnectDB cont = new ConnectDB();
		cont.connectDB();
		try{
		int i = cont.getStatement().executeUpdate(sql);
		if (i >0) result = true;
		}catch(Exception ex){
			result = false;
			
		}
			return result;
		
	}
	public static int insertCaNhan(String cmnd, String ten, String coQuan,
			String chucVu, boolean dangVien, String namSinh, boolean gioiTinh,
			String anh) {
		int result = 0;
		ConnectDB.connectDB();
	
		String SQL = "INSERT INTO canhan(cmnd,ten,namsinh,coquan,gioitinh,dangvien,chucvu,anh) " +
					  " VALUES(?,?,?,?,?,?,?,?)";
		
//
//		String SQL1 = "INSERT INTO canhan(cmnd,ten,namsinh,coquan,gioitinh,dangvien,chucvu,anh) " +
//					  " VALUES("+cmnd+","+ten+","+coQuan+","+chucVu+","+dangVien+","+namSinh+","+gioiTinh+","+anh+")";
//		System.out.println("SQL insert "+SQL1);
//		
		File fileImage = null;
		FileInputStream fis = null;
		BufferedInputStream bis = null;
		try {
			fileImage = new File("E:\\IMAGES\\Kagaya\\Kagaya_03.gif");
			fis = new FileInputStream(fileImage);
			bis = new BufferedInputStream(fis);
		} catch (FileNotFoundException exFile) {
			exFile.printStackTrace();
			
		}
		try {
			// statement.executeUpdate(sql);
			
			PreparedStatement pre = ConnectDB.connect.prepareStatement(SQL);
			pre.setString(1, cmnd);
			pre.setString(2, ten);
			pre.setString(3, namSinh);
			pre.setString(4,coQuan);
			pre.setBoolean(5, gioiTinh);
			pre.setBoolean(6, dangVien);
			pre.setString(7, chucVu);
			pre.setBinaryStream(8, bis, (int) fileImage.length());
			result = pre.executeUpdate();
		} catch (SQLException se) {
			se.printStackTrace();
			System.out.println(" SQL is invalid");
		}
		return result;
	}
	public static void main(String[] args) {
		java.sql.Date dateNgayCap = DateUtils.utilDateToSqlDate(new java.util.Date());
		int namHetHan = DateUtils.LayNamHienTai()+5;
		String strNgayCap = DateUtils.formatDateUtilToString(new java.util.Date());		
		
		int month = DateUtils.LayThangHienTai();
		int day = DateUtils.LayNgayHienTai();
		int year = DateUtils.LayNamHienTai()+5;
		String strNgayHetHan = month+"-"+day+"-"+year;
		System.out.println(strNgayHetHan);
		java.sql.Date dateNgayHetHan = java.sql.Date.valueOf(strNgayHetHan);
		
	}
}
